/***********************************************************************************************************************************
 *    Name: 
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
**/

ALTER EVENT SESSION [DBAdmin_Waits] ON SERVER STATE=STOP;
DROP EVENT SESSION [DBAdmin_Waits] ON SERVER;

CREATE EVENT SESSION [DBAdmin_Waits] ON SERVER
ADD EVENT sqlos.wait_info (-- Capture the database_id, session_id, plan_handle, and sql_text
    ACTION (sqlserver.session_id, sqlserver.database_id, sqlserver.database_name, sqlserver.sql_text, sqlserver.plan_handle)
    WHERE (opcode = 1 AND duration > 0 AND session_id > 50)
	)
--ADD TARGET package0.ring_buffer (SET max_memory = 4096) WITH (EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY = 5 SECONDS);
ADD TARGET package0.event_file (SET filename = N'D:\MSSQL12.MSSQLSERVER\MSSQL\LOG\Query_Performance.xel');
GO
ALTER EVENT SESSION [DBAdmin_Waits] ON SERVER STATE=START;
GO



SELECT  event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
        DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
        COALESCE(event_data.value('(event/data[@name="database_id"]/value)[1]', 'int'), event_data.value('(event/action[@name="database_id"]/value)[1]', 'int')) AS database_id,
        event_data.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(4000)') AS [database_name],
        event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS [session_id],
        event_data.value('(event/data[@name="wait_type"]/text)[1]', 'nvarchar(4000)') AS [wait_type],
        --event_data.value('(event/data[@name="opcode"]/text)[1]', 'nvarchar(4000)') AS [opcode],
        event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS [duration],
        --event_data.value('(event/data[@name="max_duration"]/value)[1]', 'bigint') AS [max_duration],
        --event_data.value('(event/data[@name="total_duration"]/value)[1]', 'bigint') AS [total_duration],
        event_data.value('(event/data[@name="signal_duration"]/value)[1]', 'bigint') AS [signal_duration],
        --event_data.value('(event/data[@name="completed_count"]/value)[1]', 'bigint') AS [completed_count],
        --event_data.value('(event/action[@name="plan_handle"]/value)[1]', 'nvarchar(4000)') AS [plan_handle],
        event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(4000)') AS [sql_text]
FROM    (SELECT XEvent.query('.') AS event_data
         FROM   (    -- Cast the target_data to XML 
                 SELECT CAST(target_data AS XML) AS TargetData
                 FROM   sys.dm_xe_session_targets st
                        JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
                 WHERE  name = 'DBAdmin_Waits'
                        AND target_name = 'ring_buffer'
                ) AS Data -- Split out the Event Nodes 
                CROSS APPLY TargetData.nodes('RingBufferTarget/event') AS XEventData (XEvent)
        ) AS tab (event_data)
 ORDER BY session_id, timestamp;
 GO



 SELECT
  timestamp						= xed.event_data.value('(@timestamp)[1]', 'datetime2'),
  wait_type						= xed.event_data.value('(data[@name="wait_type"]/text)[1]', 'varchar(25)'), 
  wait_type_duration_ms			= xed.event_data.value('(data[@name="duration"]/value)[1]', 'int'), 
  wait_type_signal_duration_ms	= xed.event_data.value('(data[@name="signal_duration"]/value)[1]', 'int'),
  sql_text						= xed.event_data.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)')
FROM (SELECT CAST(target_data AS XML) AS TargetData
                 FROM   sys.dm_xe_session_targets st
                        JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
                 WHERE  name = 'DBAdmin_Waits'
                        AND target_name = 'ring_buffer') AS data
  CROSS APPLY targetdata.nodes('//RingBufferTarget/event') AS xed (event_data)
